package com.sepro.tim.database.DAO;

import java.sql.Date;
import java.util.Vector;

import com.sepro.tim.database.DTO.DTO_TimeTable;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DAO_TimeTable extends SQLiteOpenHelper{
	
	private SQLiteDatabase gDatabase;
	
//	private static final String gTimeTableID = "TimeTableID";
//	
//	private static final String gTimeTableName ="TimeTableName";
//	
//	private static final String gTimeTableDescription = "TimeTableDescription";
//	
//	private static final String gTimeTableAvatar = "TimeTableAvatar";
//	
//	private static final String gTimeTableStartDate = "12/12/2012";
//	
//	private static final String gTimeTableEndDate = "21/12/2012";
	
	private String TABLE_NAME = "TIME_TABLE";
	
	private String DATABASE_CREATE = "CREATE TABLE [" + TABLE_NAME + "] (" +
			"[TimeTableID] VARCHAR(10)  NOT NULL PRIMARY KEY," +
			"[TimeTableName] VARCHAR(100)  NOT NULL," +
			"[TimeTableDescription] NVARCHAR(300)  NULL," +
			"[TimeTableAvatar] VARCHAR(50)  NULL," +
			"[TimeTableStartDate] DATE  NOT NULL," +
			"[TimeTableEndDate] DATE  NOT NULL)";
	
	public DAO_TimeTable(Context context, String DATABASE_NAME, CursorFactory factory,
			int DATABASE_VERSION) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
		
		gDatabase = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, factory);
	}
	
	private void openWriteDatabase(){
		gDatabase = getWritableDatabase();
	}
	
	private void openReadDatabase(){
		gDatabase = getReadableDatabase();
	}
	
	private void closeDatabase(){
		gDatabase.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(DAO_TimeTable.class.getName(), "Upgrading database from version " + oldVersion + "to" 
				+ newVersion + ", which will destroy all old data");
		
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
		onCreate(db);
		
	}
	
	public void addTimeTable(DTO_TimeTable mTimeTable) {
		
		this.openWriteDatabase();
		
		String sql = "INSERT INTO " + TABLE_NAME + "(TimeTableID,TimeTableName,TimeTableDescription," +
				"TimeTableAvatar,TimeTableStartDate,TimeTableEndDate) VALUES(";
		
		sql += "'" + mTimeTable.getgTimeTableID().trim() + "',";
		sql += "'" + mTimeTable.getgTimeTableName().trim() + "',";
		sql += "'" + mTimeTable.getgTimeTableDescription().trim() + "',";
		sql += "'" + mTimeTable.getgTimeTableAvatar().trim() + "',";
		sql += mTimeTable.getgTimeTableStartApply().toString() + ",";
		sql += mTimeTable.getgTimeTableEndApply().toString() + ")";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
//		
//		ctValues.put(gTimeTableID, mTimeTable.getgTimeTableID());
//		
//		ctValues.put(gTimeTableName, mTimeTable.getgTimeTableName());
//		
//		ctValues.put(gTimeTableDescription, mTimeTable.getgTimeTableDescription());
//		
//		ctValues.put(gTimeTableAvatar, mTimeTable.getgTimeTableAvatar());
//		
//		ctValues.put(gTimeTableStartDate, mTimeTable.getgTimeTableStartApply().toGMTString());
//		
//		ctValues.put(gTimeTableEndDate, mTimeTable.getgTimeTableEndApply().toGMTString());
//		
//		gDatabase.insert(TABLE_NAME, null, ctValues);
		
		this.closeDatabase();
	}
	
	public void updateTimeTable(DTO_TimeTable mTimeTable){
		
		this.openWriteDatabase();
		
		String sql = "UPDATE " + TABLE_NAME + " SET ";
		
		sql += "TimeTableName = '" + mTimeTable.getgTimeTableName().trim() + "',";
		sql += "TimeTableDescription = '" + mTimeTable.getgTimeTableDescription().trim() + "',";
		sql += "TimeTableAvatar = '" + mTimeTable.getgTimeTableAvatar().trim() + "',";
		sql += "TimeTableStartDate = " + mTimeTable.getgTimeTableStartApply().toString() + ",";
		sql += "TimeTableEndDate = " + mTimeTable.getgTimeTableEndApply().toString() + " ";
		
		sql += "WHERE TimeTableID = '" + mTimeTable.getgTimeTableID().trim() + "'";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
//		
//		ctValues.put(gTimeTableID, mTimeTable.getgTimeTableID());
//		
//		ctValues.put(gTimeTableName, mTimeTable.getgTimeTableName());
//		
//		ctValues.put(gTimeTableDescription, mTimeTable.getgTimeTableDescription());
//		
//		ctValues.put(gTimeTableAvatar, mTimeTable.getgTimeTableAvatar());
//		
//		ctValues.put(gTimeTableStartDate, mTimeTable.getgTimeTableStartApply().toGMTString());
//		
//		ctValues.put(gTimeTableEndDate, mTimeTable.getgTimeTableEndApply().toGMTString());
//		
//		gDatabase.update(TABLE_NAME, ctValues, gTimeTableID + "=?",
//				new String[]{String.valueOf(mTimeTable.getgTimeTableID())});
		
		this.closeDatabase();
	}
	
	public void deleteTimeTable(String mTimeTableID) {
		
		this.openWriteDatabase();
		
		String sql ="DELETE FROM " + TABLE_NAME + " ";
		sql += "WHERE TimeTableID = '" + mTimeTableID.trim() + "'";
		
		gDatabase.execSQL(sql);
		
//		gDatabase.delete(TABLE_NAME, gTimeTableID + "=?", new String[]{String.valueOf(mTimeTableID)});
		
		this.closeDatabase();
	}
	
	public Vector<DTO_TimeTable> searchStatApply(Date StartApply) {
		
		this.openReadDatabase();
		
		Vector<DTO_TimeTable> list = new Vector<DTO_TimeTable>();
		
		DTO_TimeTable mTimeTable = new DTO_TimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql +="WHERE TimeTableStartDate = " + StartApply.toString() ;
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTimeTable.setgTimeTableName(mCursor.getString(1).trim());
				mTimeTable.setgTimeTableDescription(mCursor.getString(2).trim());
				mTimeTable.setgTimeTableAvatar(mCursor.getString(3).trim());
				mTimeTable.setgTimeTableStartApply(new Date(mCursor.getLong(4)));
				mTimeTable.setgTimeTableEndApply(new Date(mCursor.getLong(5)));
				
				list.add(mTimeTable);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	public Vector<DTO_TimeTable> searchEndApply(Date EndApply) {
		
		this.openReadDatabase();
		
		Vector<DTO_TimeTable> list = new Vector<DTO_TimeTable>();
		
		DTO_TimeTable mTimeTable = new DTO_TimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql +="WHERE TimeTableEndDate = " + EndApply.toString() ;
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTimeTable.setgTimeTableName(mCursor.getString(1).trim());
				mTimeTable.setgTimeTableDescription(mCursor.getString(2).trim());
				mTimeTable.setgTimeTableAvatar(mCursor.getString(3).trim());
				mTimeTable.setgTimeTableStartApply(new Date(mCursor.getLong(4)));
				mTimeTable.setgTimeTableEndApply(new Date(mCursor.getLong(5)));
				
				list.add(mTimeTable);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	public DTO_TimeTable getTimeTable(String mTimeTableID) {

		this.openReadDatabase();
		
		DTO_TimeTable mTimeTable = new DTO_TimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " " +
				"WHERE TimeTableID = '" + mTimeTableID + "'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			mTimeTable.setgTimeTableID(mCursor.getString(0).trim());
			mTimeTable.setgTimeTableName(mCursor.getString(1).trim());
			mTimeTable.setgTimeTableDescription(mCursor.getString(2).trim());
			mTimeTable.setgTimeTableAvatar(mCursor.getString(3).trim());
			mTimeTable.setgTimeTableStartApply(new Date(mCursor.getLong(4)));
			mTimeTable.setgTimeTableEndApply(new Date(mCursor.getLong(5)));
			
			this.closeDatabase();
			return mTimeTable;		
		}
		else {
			this.closeDatabase();
			return null;
		}	
	}
	
	public Vector<DTO_TimeTable> getAllTimeTable() {

		this.openReadDatabase();
		
		Vector<DTO_TimeTable> list = new Vector<DTO_TimeTable>();
		
		DTO_TimeTable mTimeTable = new DTO_TimeTable();
		
		String sql = "SELECT * FROM " + TABLE_NAME;
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTimeTable.setgTimeTableID(mCursor.getString(0).trim());
				mTimeTable.setgTimeTableName(mCursor.getString(1).trim());
				mTimeTable.setgTimeTableDescription(mCursor.getString(2).trim());
				mTimeTable.setgTimeTableAvatar(mCursor.getString(3).trim());
				mTimeTable.setgTimeTableStartApply(new Date(mCursor.getLong(4)));
				mTimeTable.setgTimeTableEndApply(new Date(mCursor.getLong(5)));
				
				list.add(mTimeTable);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
}
